INSERT Statement

Course- SQL >

This SQL tutorial explains how to use the SQL INSERT statement with syntax, examples, and practice exercises. There are 2 syntaxes for the INSERT statement depending on whether you are inserting one record or multiple records.

Description

The SQL INSERT statement is used to insert a one or more records into a table.

Syntax

The syntax for the SQL INSERT statement when inserting a single record using the VALUES keyword is:

INSERT INTO table
(column1, column2, ... )
VALUES
(expression1, expression2, ... );

Or the syntax for the SQL INSERT statement when inserting multiple records using a SELECT statement is:

INSERT INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_tables
[WHERE conditions];

Parameters or Arguments

table: The table in which to insert the records.
column1, column2: These are the columns in the table to insert values.
expression1, expression2: These are the values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.
source_tables: Used when inserting records from another table. This is the source table when performing the insert.
WHERE conditions: Optional. Used when inserting records from another table. These are the conditions that must be met for the records to be inserted.

Note

When inserting records into a table using the SQL INSERT statement, you must provide a value for every NOT NULL column.
You can omit a column from the SQL INSERT statement if the column allows NULL values.

Example - Using VALUES keyword

Let's look at an example showing how to use the SQL INSERT statement. The simplest way use the INSERT statement is to insert one record into a table using the VALUES keyword.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(24553, 'IBM');

This INSERT statement example would insert one record into the suppliers table. This new record would have a supplier_id of 24553 and a supplier_name of IBM.

Example - Using SELECT statement

You can also create more complicated SQL INSERT statements using SELECT statement.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';

By placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly.

With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following SQL SELECT statement before performing the insert.

SELECT count(*)
FROM customers
WHERE city = 'Newark';

Frequently Asked Questions

Question: I am setting up a database with clients. I know that you use the SQL INSERT statement to insert information in the database, but how do I make sure that I do not enter the same client information again?

Answer: You can make sure that you do not insert duplicate information by using the SQL EXISTS condition.

For example, if you had a table named clients with a primary key of client_id, you could use the following SQL INSERT statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = suppliers.supplier_id);

This SQL INSERT statement inserts multiple records with a subselect.

If you wanted to insert a single record, you could use the following SQL INSERT statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = 10345);

The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.

Practice Exercise #1:

Based on the employees table, insert an employee record whose employee_number is 1001, employee_name is Sally Johnson and salary is $32,000:

CREATE TABLE employees
( employee_number int NOT NULL,
  employee_name char(50) NOT NULL,
  salary int,
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution for Practice Exercise #1:

The following SQL INSERT statement would insert this record into the employees table:

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1001, 'Sally Johnson', 32000);

Practice Exercise #2:

Based on the suppliers table, insert a supplier record whose supplier_id is 5001 and supplier_name is Apple:

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  city char(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

Solution for Practice Exercise #2:

The following SQL INSERT statement would insert this record into the suppliers table:

INSERT INTO suppliers (supplier_id, supplier_name)
VALUES (5001, 'Apple');

Practice Exercise #3:

Based on the customers and old_customers table, insert into the customers table all records from the old_customers table whose status is DELETED.

CREATE TABLE customers
( customer_id int NOT NULL,
  customer_name char(50) NOT NULL,
  city char(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

CREATE TABLE old_customers
( old_customer_id int NOT NULL,
  old_customer_name char(50) NOT NULL,
  old_city char(50),
  status char(20),
  CONSTRAINT old_customers_pk PRIMARY KEY (old_customer_id)
);

Solution for Practice Exercise #3:

The following SQL INSERT statement would be the solution to insert into the customers table using a sub-select:

INSERT INTO customers
(customer_id, customer_name, city)
SELECT old_customer_id, old_customer_name, old_city
FROM old_customers
WHERE status = 'DELETED';

PRV MENU NEXT